package com.sduept.nwld.mobile.manager.fault;
import com.sduept.bigdata.fault.entity.FaultRecord;
import com.sduept.core.dao.SuperDAO;
import com.sduept.permission.entity.Principal;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

/**
 * 
 * Simple to Introduction
 * 
 * @Description: [故障记录信息的Manager]
 * @Author: [lixz]
 * @CreateDate: [2019年6月17日 下午4:07:20]
 * 
 * 
 */
@Slf4j
@Service
public class FaultRecordManager {
	@Autowired
	private SuperDAO dao;
	// ------------------------------权限查询----------------------------------------------------
	// 1、第一层查询 故障记录、权限记录进行关联，按照时间，部门id过滤，并且按照权限倒序排序，方便外层查询分组
	// 2、按照权限表的resource（变电站id）分组，同组内只会把DENY的取出来，并且将DENY的过滤掉
	// 3、将第二步查询出的结果按照故障记录id进行分组，去重即返回结果
	private final String faultRecordQueryTemplate = "select * from " + "(SELECT w.* from "
			+ "(SELECT a.resource,a.permission,t.* FROM t_org_acl a LEFT JOIN t_cim_fault t ON (a.resource=t.stationId or a.resource= t.stationId2) WHERE #{esql} AND a.operator in (#{departIds}) ORDER BY a.permission desc LIMIT 100000)"
			+ " as w GROUP BY w.id,w.resource having w.permission<>'DENY')"
			+ "as s GROUP BY s.id ORDER BY #{orderColum}";
	/**
	 * 默认的排序方式
	 */
	private final String defaultOrder = "faultTime DESC";

	/**
	 * 根据故障的主键id获取故障记录信息
	 * 
	 * @param id
	 * @return
	 */
	public FaultRecord getFaultRecordById(String id) {
		return dao.findById(id, FaultRecord.class);
	}
	
	/**
	 * 分页获得最新的的故障信息（>=500kV）
	 * 
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findLastestGe500kVFaultForApp(Principal principal,int[] range) {
	
		WhereBuilder wb = new WhereBuilder();
		if (principal != null) {
			wb.buildFilterFaultSql().and().buildGeVoltageSql();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql, new Object[] { 500 });
		} else {
			String whereSql = wb.buildFilterFaultSql().and().buildGeVoltageSql().build();
			return dao.findByCondition(whereSql, new Object[] { 500 }, "ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}

	/**
	 * 根据账户权限、日期,分页查询出大于等于500kv的故障
	 * 
	 * @param principal
	 * @param start
	 * @param end
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findGe500kVFaultByTimedForApp(Principal principal, Date start, Date end, int[] range) {
		if (start == null || end == null) {
			throw new IllegalArgumentException("start or end is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildFilterFaultSql().and().buildGeVoltageSql();
		if (principal != null) {
			wb.and().buildFaultTimeBetweenSql();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql, new Object[] { 500, start, end });
		} else {
			String whereSql = wb.and().buildFaultTimeBetweenSql().build();
			return dao.findByCondition(whereSql, new Object[] { 500, start, end }, "ORDER BY faultTime DESC", range,
					FaultRecord.class);

		}

	}
	
	/**
	 * 根据账户权限、 厂站，分页查询大于等于500kV的故障信息
	 * @param principal
	 * @param stationName
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findFaultsByStationName(Principal principal, String stationName, int[] range) {
		if (StringUtils.isEmpty(stationName)) {
			throw new IllegalArgumentException("stationName is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildGeVoltageSql().and().buildFilterFaultSql();
		if (principal != null) {
			wb.and().buildLp().buildStationNameSql().buildRp();

			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql, new Object[] { 500, stationName, stationName });
		} else {
			log.info("---->管理员权限查询");
			String whereSql = wb.and().buildLp().buildStationNameSql().buildRp().build();
			return dao.findByCondition(whereSql, new Object[] { 500, stationName, stationName },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}

	}

	/**
	 * 根据账户权限、设备，分页查询大于等于500kV的故障信息
	 * @param principal
	 * @param deviceName
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findFaultsByDevice(Principal principal, String deviceName, int[] range) {
		if (StringUtils.isEmpty(deviceName)) {
			throw new IllegalArgumentException("device is null");
		}
		WhereBuilder wb = new WhereBuilder();
		if (principal != null) {
			wb.buildDeviceNameLikeSql().and().buildGeVoltageSql().and().buildFilterFaultSql();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql, new Object[] { "%" + deviceName + "%", 500 });
		} else {
			log.info("---->管理员权限查询");
			String whereSql = wb.buildDeviceNameLikeSql().and().buildGeVoltageSql().and().buildFilterFaultSql().build();
			return dao.findByCondition(whereSql, new Object[] { "%" + deviceName + "%", 500 },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}
	
	/**
	 * 根据账户权限、日期和变电站，分页查询大于等于500kV的故障信息
	 * @param principal
	 * @param start
	 * @param end
	 * @param stationName
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findGe500kVFaultByDateAndStationForApp(Principal principal, Date start, Date end,
			String stationName, int[] range) {
		if (start == null || end == null || StringUtils.isEmpty(stationName)) {
			throw new IllegalArgumentException("start or end or stationName is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildFilterFaultSql().and().buildGeVoltageSql();
		if (principal != null) {
			wb.and().buildLp().buildStationNameSql().buildRp().and().buildFaultTimeBetweenSql();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql,
					new Object[] { 500, stationName, stationName, start, end });
		} else {
			log.info("---->管理员权限查询");
			wb.and().buildLp().buildStationNameSql().buildRp().and().buildFaultTimeBetweenSql();
			String whereSql = wb.build();
			return dao.findByCondition(whereSql, new Object[] { 500, stationName, stationName, start, end },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}

	/**
	 * 根据账户权限、日期和设备，分页查询大于等于500kV的故障信息
	 * @param principal
	 * @param start
	 * @param end
	 * @param deviceName
	 * @return
	 */
	public List<FaultRecord> findGe500kVFaultByDateAndDeviceForApp(Principal principal, Date start, Date end,
			String deviceName, int[] range) {
		if (start == null || end == null || StringUtils.isEmpty(deviceName)) {
			throw new IllegalArgumentException("start or end or deviceName is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildFilterFaultSql().and().buildGeVoltageSql();
		if (principal != null) {
			wb.and().buildDeviceNameLikeSql().and().buildFaultTimeBetweenSql();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql,
					new Object[] { 500, "%" + deviceName + "%", start, end });
		} else {
			log.info("---->管理员权限查询");
			wb.and().buildDeviceNameLikeSql().and().buildFaultTimeBetweenSql();
			String whereSql = wb.build();
			return dao.findByCondition(whereSql, new Object[] { 500, "%" + deviceName + "%", start, end },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}
	
	 /**
     * 根据账户权限、 厂站、设备，分页查询大于等于500kV的故障信息
     * @param principal
     * @param stationName
     * @param deviceName
     * @param range
     * @return
     */
	public List<FaultRecord> findFaultsByStationNameAndDevice(Principal principal, String stationName,
			String deviceName, int[] range) {
		if (StringUtils.isEmpty(deviceName) || StringUtils.isEmpty(stationName)) {
			throw new IllegalArgumentException("deviceName or stationName is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildFilterFaultSql().and().buildGeVoltageSql();
		if (principal != null) {
			wb.and().buildDeviceNameLikeSql().and().buildLp().buildStationNameSql().buildRp();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql,
					new Object[] { 500, "%" + deviceName + "%", stationName, stationName });
		} else {
			log.info("---->管理员权限查询");
			String whereSql = wb.and().buildDeviceNameLikeSql().and().buildLp().buildStationNameSql().buildRp().build();
			return dao.findByCondition(whereSql, new Object[] { 500, "%" + deviceName + "%", stationName, stationName },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}
	
	/**
	 * 根据账户权限、厂站、设备、开始日期、结束日期,分页查询故障信息
	 * @param principal
	 * @param start
	 * @param end
	 * @param deviceName
	 * @param stationName
	 * @param range
	 * @return
	 */
	public List<FaultRecord> findGe500kVFaultByDateAndDeviceAndStationForApp(Principal principal, Date start, Date end,
			String deviceName, String stationName, int[] range) {
		if (start == null || end == null || StringUtils.isEmpty(deviceName) || StringUtils.isEmpty(stationName)) {
			throw new IllegalArgumentException("start or end or deviceName or stationName is null");
		}
		WhereBuilder wb = new WhereBuilder();
		wb.buildFilterFaultSql().and().buildGeVoltageSql();
		if (principal != null) {
			log.info("---->权限查询");
			// 权限查询
			wb.and().buildDeviceNameLikeSql().and().buildLp().buildFaultTimeBetweenSql().buildRp().and().buildLp()
					.buildStationNameSql().buildRp();
			String insql = buildInDepartsSql(principal);
			String nativeSql = faultRecordQueryTemplate.replace("#{esql}", wb.build()).replace("#{departIds}", insql)
					.replace("#{orderColum}", defaultOrder);
			if(range != null) {
				if (range.length > 1) {
					nativeSql += " limit " + range[0] + " , " + range[1];
				} else if (range.length == 1) {
					nativeSql += " limit " + range[0];
				}
			}
			return dao.nativeQuery(FaultRecord.class, nativeSql,
					new Object[] { 500, "%" + deviceName + "%", start, end, stationName, stationName });
		} else {
			log.info("---->管理员权限查询");
			// 非权限查询
			wb.and().buildDeviceNameLikeSql().and().buildLp().buildFaultTimeBetweenSql().buildRp().and().buildLp()
					.buildStationNameSql().buildRp();
			String whereSql = wb.build();
			return dao.findByCondition(whereSql,
					new Object[] { 500, "%" + deviceName + "%", start, end, stationName, stationName },
					"ORDER BY faultTime DESC", range, FaultRecord.class);
		}
	}
	
	/**
	 * 根据年月查找一次设备 若只通过年查询，月份设置为0
	 * 
	 * @param currentYear
	 * @param currentMonth
	 * @return
	 */
	public int primFaultStatistic(int currentYear, int currentMonth) {

		String whereSql = " status = 0 and voltage >= 500 ";

		Object[] whereArgs;
		if (currentMonth == 0) {// 只查年

			whereSql += " and date_format(faultTime,'%Y') = ?  ";
			whereArgs = new Object[1];
			whereArgs[0] = String.valueOf(currentYear);
		} else {

			whereSql += " and date_format(faultTime,'%Y-%m') = ? ";
			whereArgs = new Object[1];
			String time;
			if (currentMonth < 10) {
				time = String.valueOf(currentYear) + "-0" + String.valueOf(currentMonth);
			} else {
				time = String.valueOf(currentYear) + "-" + String.valueOf(currentMonth);
			}
			whereArgs[0] = time;
		}
		int statisticNum = (int) dao.count(whereSql, whereArgs, FaultRecord.class);

		return statisticNum;
	}
	
	/**
	 * 构建查询语句的 in 查询集合
	 * 
	 * @param principal
	 * @return
	 */
	private String buildInDepartsSql(Principal principal) {
		List<String> departIds = principal.getDepartIds();
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < departIds.size(); i++) {
			if (i == departIds.size() - 1) {
				sb.append("'").append(departIds.get(i)).append("'");
			} else {
				sb.append("'").append(departIds.get(i)).append("',");
			}
		}
		return sb.toString();
	}
	//------------------------------------------------------------------------------------------------------------------------
	/**
	 * 如需要自行补充构建方法
	 * 
	 * @author Administrator
	 *
	 */
	@SuppressWarnings("unused")
	private final class WhereBuilder {
		private StringBuilder sb = new StringBuilder();

		private String build() {
			return sb.toString();
		}

		public WhereBuilder buildLp() {
			sb.append(" ( ");
			return this;
		}

		public WhereBuilder buildRp() {
			sb.append(" )");
			return this;
		}

		public WhereBuilder buildFilterFaultSql() {
			sb.append(" status = 0 AND push= 0 ");
			return this;
		}

		public WhereBuilder buildStatusSql() {
			sb.append(" status = ?");
			return this;
		}

		public WhereBuilder buildEtypeSql() {
			sb.append(" etype =? ");
			return this;
		}

		public WhereBuilder buildEtypeSql(String etype) {
			sb.append(" etype ='" + etype + "'");
			return this;
		}

		public WhereBuilder buildFaultTimeBetweenSql() {
			sb.append(" faultTime between ? AND ? ");
			return this;
		}

		public WhereBuilder buildLtFaultTimeSql() {
			sb.append(" faultTime < ? ");
			return this;
		}

		public WhereBuilder buildLeFaultTimeSql() {
			sb.append(" faultTime <= ? ");
			return this;
		}

		public WhereBuilder buildEqFaultTimeSql() {
			sb.append(" faultTime = ? ");
			return this;
		}

		public WhereBuilder buildGeFaultTimeSql() {
			sb.append(" faultTime >= ? ");
			return this;
		}

		public WhereBuilder buildOpenFaultTimeSql() {
			sb.append(" faultTime >?  AND faultTime<? ");
			return this;
		}

		public WhereBuilder buildDeviceIdSql() {
			sb.append(" equipment_id=? ");
			return this;
		}

		public WhereBuilder buildDeviceNameSql() {
			sb.append(" equipment=? ");
			return this;
		}

		public WhereBuilder buildDeviceNameLikeSql() {
			sb.append(" equipment like ? ");
			return this;
		}

		public WhereBuilder buildStationNameSql() {
			sb.append(" stationName=? OR stationName2=? ");
			return this;
		}

		public WhereBuilder buildStationIdSql() {
			sb.append(" (stationId=? OR stationId2=?) ");
			return this;
		}

		public WhereBuilder buildResonSql() {
			sb.append(" faultReson=? ");
			return this;
		}

		public WhereBuilder buildFilterPushSql() {
			sb.append(" push= 0 ");
			return this;
		}

		public WhereBuilder and() {
			sb.append(" AND ");
			return this;
		}

		public WhereBuilder or() {
			sb.append(" OR ");
			return this;
		}

		public WhereBuilder buildVoltageSql(Integer voltage) {
			if (voltage == null) {
				return this;
			} else if (voltage - 499 == 0) {
				sb.append(" voltage > ? ");
			} else {
				sb.append(" voltage = ? ");
			}
			return this;
		}

		public WhereBuilder buildGeVoltageSql() {
			sb.append("  voltage >= ? ");
			return this;
		}
	}
}
